package dao.impl;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import dao.ILoader;
import dao.models.Answer;
import dao.models.Chapter;
import dao.models.Question;

public class DBLoader implements ILoader {

    private abstract class InTx<T> {
	public abstract T exec(Connection c) throws Exception;

	public T invoke() {
	    Connection c = null;
	    try {
		return exec(c = getConnection());
	    } catch (Exception e) {
		throw new RuntimeException(e);
	    } finally {
		if (c != null) {
		    try {
			c.close();
		    } catch (SQLException e) {
			e.printStackTrace();
		    }
		}
	    }
	}

	private Connection getConnection() throws Exception {
	    // TODO MayBePool?
	    Class.forName("org.postgresql.Driver");
	    Connection connection = null;
	    connection = DriverManager.getConnection(
		    "jdbc:postgresql://localhost:5433/web", "postgres",
		    "12345679");
	    return connection;
	}
    }

    @Override
    public List<Chapter> getChapters() {
	return new InTx<List<Chapter>>() {

	    @Override
	    public List<Chapter> exec(Connection c) throws Exception {
		PreparedStatement st = c
			.prepareStatement("Select id from chapters;");
		ResultSet q = st.executeQuery();
		List<Chapter> res = new ArrayList<Chapter>();
		while (q.next()) {
		    res.add(getChapter(q.getString(1)));
		}
		return res;
	    }
	}.invoke();
    }

    @Override
    public Chapter getChapter(final String id) {
	return new InTx<Chapter>() {
	    @Override
	    public Chapter exec(Connection c) throws Exception {
		PreparedStatement st = c
			.prepareStatement("Select id, name, text from chapters where id = ?");
		st.setInt(1, Integer.valueOf(id));
		ResultSet q = st.executeQuery();
		while (q.next()) {
		    Chapter chapter = new Chapter();
		    chapter.setId(q.getInt(1));
		    chapter.setName(q.getString(2));
		    chapter.setText(q.getString(3));
		    chapter.setQuestions(getQustionsByChapterId(chapter.getId()));
		    return chapter;
		}
		return null;
	    }
	}.invoke();
    }

    private List<Question> getQustionsByChapterId(final Integer id) {
	return new InTx<List<Question>>() {

	    @Override
	    public List<Question> exec(Connection c) throws Exception {
		PreparedStatement st = c
			.prepareStatement("SELECT id, text, chapter_id FROM questions where chapter_id = ?");
		st.setInt(1, id);
		ResultSet q = st.executeQuery();
		List<Question> res = new ArrayList<Question>();
		while (q.next()) {
		    Question question = new Question();
		    question.setId(q.getInt(1));
		    question.setText(q.getString(2));
		    question.setAnswers(getAnswersByQuestionId(question.getId()));
		    res.add(question);
		}
		return res;
	    }

	}.invoke();
    }

    private List<Answer> getAnswersByQuestionId(final int id) {
	return new InTx<List<Answer>>() {

	    @Override
	    public List<Answer> exec(Connection c) throws Exception {
		PreparedStatement st = c
			.prepareStatement("SELECT id, answer, question_id  FROM answers where question_id = ?");
		st.setInt(1, id);
		ResultSet q = st.executeQuery();
		List<Answer> res = new ArrayList<Answer>();
		while (q.next()) {
		    Answer answer = new Answer();
		    answer.setId(q.getInt(1));
		    answer.setAnswer(q.getString(2));
		    res.add(answer);
		}
		return res;
	    }

	}.invoke();
    }

    @Override
    public Answer getAnswerById(final String answerId) {
	return new InTx<Answer>() {

	    @Override
	    public Answer exec(Connection c) throws Exception {
		PreparedStatement st = c
			.prepareStatement("SELECT id, answer, question_id  FROM answers where id = ?");
		st.setInt(1, Integer.valueOf(answerId));
		ResultSet q = st.executeQuery();
		while (q.next()) {
		    Answer answer = new Answer();
		    answer.setId(q.getInt(1));
		    answer.setAnswer(q.getString(2));
		    return answer;
		}
		return null;
	    }

	}.invoke();
    }

    @Override
    public boolean addNewQuestionToChapter(final String chapterIdAsString,
	    final Question q) {
	return new InTx<Boolean>() {

	    @Override
	    public Boolean exec(Connection c) throws Exception {
		int chpaterId = Integer.valueOf(chapterIdAsString);
		PreparedStatement st = c
			.prepareStatement("INSERT INTO questions(text, chapter_id) VALUES (?, ?) RETURNING id");
		st.setString(1, q.getText());
		st.setInt(2, chpaterId);
		ResultSet res = st.executeQuery();
		if (res.next()) {
		    addAnswersToQuestion(c, res.getInt(1), q.getAnswers());
		    return true;
		} else {
		    return false;
		}
	    }
	}.invoke();
    }

    private void addAnswersToQuestion(Connection c, int questionId,
	    List<Answer> answers) throws SQLException {
	for (Answer answ : answers) {
	    PreparedStatement st2 = c
		    .prepareStatement("INSERT INTO answers(answer, question_id) VALUES (?, ?);");
	    st2.setString(1, answ.getAnswer());
	    st2.setInt(2, questionId);
	    st2.executeUpdate();
	}
    }

    @Override
    public boolean addNewChapter(final Chapter chapter) {
	return new InTx<Boolean>() {

	    @Override
	    public Boolean exec(Connection c) throws Exception {
		PreparedStatement insertChapter = c
			.prepareStatement("INSERT INTO chapters(name, text) VALUES (?, ?);");
		insertChapter.setString(1, chapter.getName());
		insertChapter.setString(2, chapter.getText());
		return insertChapter.executeUpdate() > 0;
	    }
	}.invoke();
    }
}
